﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Reflection;

using Vita.Common;
using Vita.Entities;
using Vita.Entities.Logging;
using Vita.Entities.Model;
using Vita.Data.Model;
using Vita.Data.Driver;
using Vita.Entities.Services;


namespace Vita.Data.Driver {

  /// <summary>Loads DB model from database. </summary>
  public partial class DbModelLoader {
    //returned by information_schema.Tables view as Table_type; for some db vendors it is different (postgres: BASE TABLE)
    protected string TableTypeTag = "TABLE";
    protected string ViewTypeTag = "VIEW";
    protected string RoutineTypeTag = "PROCEDURE";

    protected DbSettings Settings;
    protected DbDriver Driver;
    protected MemoryLog Log;

    public DbVersionInfo VersionInfo;
    protected DbModel Model;
    protected SqlSourceHasher SourceHasher = new SqlSourceHasher(); 

    //Static empty table, to use as return object for methods that return 'nothing' (not supported aspects)
    protected DataTable EmptyTable = new DataTable();

    //Schemas are initially copied from Settings. But app can reset them to desired list later.  
    //note: if _schemas is empty, it means - load all.
    private StringSet _schemas = new StringSet(); 
    private string _schemasListForFilter;


    public DbModelLoader(DbSettings settings, MemoryLog log) {
      Settings = settings;
      Log = log;
      Driver = Settings.ModelConfig.Driver;
      SetupSchemas(Settings.GetSchemas());
    }

    public virtual DbModel LoadModel() {
      Model = new DbModel(Settings.ModelConfig);
      Model.VersionInfo = VersionInfo; 
      LoadSchemas();
      LoadTables();
      if (Driver.Supports(DbFeatures.Views))
        LoadViews();
      LoadTableColumns();
      LoadTableConstaints();
      LoadTableConstaintColumns();
      LoadReferentialConstraints();
      LoadIndexes();
      LoadIndexColumns();
      LoadRoutines();
      LoadSequences();
      LoadUserDefinedTypes(); 
      OnModelLoaded(); 
      return Model; 
    }//method

    public void SetupSchemas(IEnumerable<string> schemas) {
      _schemas.Clear();
      _schemasListForFilter = null;
      if (!SupportsSchemas())
        return;
      _schemas.UnionWith(schemas);
      if (_schemas.Count > 0) {
        _schemasListForFilter = "('" + string.Join("', '", _schemas) + "')";
      }
    }

    protected void LogError(string message, params object[] args) {
      if (Log == null) //if no log then throw
        Util.Throw(message, args); 
      Log.Error(message, args);
    }

    protected virtual bool IncludeSchema(string schema) {
      if (!SupportsSchemas() || _schemas.Count == 0)
        return true; 
      return _schemas.Contains(schema);
    }

    protected bool SupportsSchemas() {
      return Driver.Supports(DbFeatures.Schemas);
    }

    protected virtual void LoadRoutines() {
      var supportsRoutines = Driver.Supports(DbFeatures.StoredProcedures);
      if (!supportsRoutines) return;
      var data = GetRoutines();
      bool hasCustomTag = data.Columns.Contains("ROUTINE_CUSTOM");
      foreach (DataRow row in data.Rows) {
        var schema = row.GetAsString("ROUTINE_SCHEMA");
        if (!IncludeSchema(schema)) continue; 
        var name = row.GetAsString("ROUTINE_NAME");
        var procText = row.GetAsString("ROUTINE_DEFINITION");
        var descrTag = GetProcedureTag(procText);
        //if will be added to model.Commands automatically
        var dbCommand = new DbCommandInfo(Model, schema, name, null, descrTag);
        dbCommand.CommandType = CommandType.StoredProcedure;
        dbCommand.StoredProcText = procText;
        dbCommand.SourceHash = SourceHasher.ExtractHash(procText); 
        //detect if it is auto-generated
        if (!string.IsNullOrEmpty(descrTag)) {
          dbCommand.DescriptiveTag = descrTag;
          dbCommand.Flags |= DbCommandFlags.AutoGenerated;
          if (descrTag.StartsWith("CRUD"))
            LinkCrudProcToTable(dbCommand, descrTag);
        }
        if (hasCustomTag)
          dbCommand.CustomTag = row["ROUTINE_CUSTOM"];
      }//foreach
    }//method

    //Tag sample: "CRUD/[books].[BookOrderLine]/Delete"
    // Note: we need to link Proc to table at load time, in case we will be deleting table - we also need to delete crud procs
    protected virtual void LinkCrudProcToTable(DbCommandInfo command, string tag) {
      var arr = tag.SplitNames('/');
      var tblName = arr[1];
      var tbl = Model.GetTable(tblName);
      if(tbl != null) {
        command.Table = tbl;
        tbl.CrudCommands.Add(command); 
      }
    }

    //Loads schemas into dbModel.Setup.Schemas only if it was originally empty
    protected virtual void LoadSchemas() {
      Model.Schemas.Clear(); //important - clear schemas that were copied from Setup
      if (!SupportsSchemas())
        return; 
      var data = GetSchemas();
      foreach (DataRow row in data.Rows) {
        var schema = row.GetAsString("SCHEMA_NAME");
        if (IncludeSchema(schema)) //add only schemas that are relevant to the model
          Model.Schemas.Add(new DbSchemaInfo(Model, schema));
      }
    }

    protected virtual void LoadTables() {
      var data = GetTables();
      foreach (DataRow row in data.Rows) {
        var schema = row.GetAsString("TABLE_SCHEMA");
        if (!IncludeSchema(schema)) continue; 
        var tableName = row.GetAsString("TABLE_NAME");
        var tblInfo = new DbTableInfo(Model, schema, tableName, null);
      }
    }

    protected virtual void LoadViews() {
      var data = GetViews();
      var supportsMatViews = Driver.Supports(DbFeatures.MaterializedViews);
      foreach(DataRow row in data.Rows) {
        var schema = row.GetAsString("TABLE_SCHEMA");
        if(!IncludeSchema(schema)) continue;
        var viewName = row.GetAsString("TABLE_NAME");
        var sql = row.GetAsString("VIEW_DEFINITION");
        var view = new DbTableInfo(Model, schema, viewName, null, DbObjectType.View);
        view.ViewSql = sql;
        if (supportsMatViews && row.GetAsString("IS_MATERIALIZED") == "Y")
          view.IsMaterializedView = true;
        //assign hash from Values dictionary in DbVersionInfo
        if (Model.VersionInfo != null) {
          var hashKey = DbModel.GetViewKey(view); 
          string hash;
          if (Model.VersionInfo.Values.TryGetValue(hashKey, out hash))
            view.ViewHash = hash; 
        }
        //if hash not found, try to extract it from source
        if (view.ViewHash == null)
          view.ViewHash = SourceHasher.ExtractHash(sql);
      }
    }

    protected virtual void LoadTableColumns() {
      DbTableInfo currTblInfo = null;
      var data = GetColumns();
      foreach (DataRow row in data.Rows) {
        var schema = row.GetAsString("TABLE_SCHEMA");
        if (!IncludeSchema(schema)) continue;
        var tableName = row.GetAsString("TABLE_NAME");
        if(currTblInfo == null || currTblInfo.Schema != schema || currTblInfo.TableName != tableName) {
          currTblInfo = Model.GetTable(schema, tableName);
        }
        if(currTblInfo == null) 
          continue; //it is a view
        var colName = row.GetAsString("COLUMN_NAME");
        var dataTypeString = row.GetAsString("DATA_TYPE");
        var size = row.GetAsLong("CHARACTER_MAXIMUM_LENGTH");
        var defaultExpr = row.GetAsString("COLUMN_DEFAULT");
        var typeInfo = GetDbTypeInfo(row);
        if (typeInfo == null) {
          LogError(
            "Failed to find TypeInfo for SQL data type [{0}]. Table {1}, column {2}.", dataTypeString, tableName, colName);
          continue;
        }
        var column = new DbColumnInfo(currTblInfo, colName, typeInfo);
        column.DefaultExpression = row.GetAsString("COLUMN_DEFAULT");
        // Let schema manager add any provider-specific info
        OnColumnLoaded(column, row);
      }//foreach
    }

    protected virtual void LoadTableConstaints() {
      var data = GetTableConstraints();
      foreach (DataRow row in data.Rows) {
        var schema = row.GetAsString("TABLE_SCHEMA");
        if (!IncludeSchema(schema)) continue;
        var tableName = row.GetAsString("TABLE_NAME");
        var table = Model.GetTable(schema, tableName);
        var constrName = row.GetAsString("CONSTRAINT_NAME");
        var constrTypeStr = row.GetAsString("CONSTRAINT_TYPE");
        if(table == null) continue;
        KeyType keyType;
        switch(constrTypeStr.Trim()) {
          case "PRIMARY KEY": keyType = KeyType.PrimaryKey;       break;
          case "FOREIGN KEY": keyType = KeyType.ForeignKey;       break;
          default: continue; //skip this constraint
        }
        var dbKey = new DbKeyInfo(constrName, table, keyType);
        if (keyType == KeyType.PrimaryKey)
          table.PrimaryKey = dbKey;
      }
      //sanity check - all tables must have PK
      foreach(var table in Model.Tables)
        if(table.PrimaryKey == null && table.Kind == EntityKind.Table) {
          //Just to have a line for a breakpoint
          System.Diagnostics.Debug.WriteLine("DBModelLoader warning: Table without PK:" + table.TableName);
        }
    }
    protected virtual void LoadTableConstaintColumns() {
      var data = GetTableConstraintColumns();
      foreach (DataRow row in data.Rows) {
        var schema = row.GetAsString("TABLE_SCHEMA");
        if (!IncludeSchema(schema)) continue;
        var tableName = row.GetAsString("TABLE_NAME");
        var table = Model.GetTable(schema, tableName);
        if (table == null)  continue; 
        var colName = row.GetAsString("COLUMN_NAME");
        var keyName = row.GetAsString("CONSTRAINT_NAME");
        var key = table.Keys.FirstOrDefault(k => k.Name == keyName); 
        if(key == null) continue;
        var col = key.Table.Columns.FirstOrDefault(c => c.ColumnName == colName);
        key.KeyColumns.Add(new DbKeyColumnInfo(col));
      }
      //Mark PK columns as no update
      foreach (var t in Model.Tables)
        if (t.PrimaryKey != null)
          foreach (var kc in t.PrimaryKey.KeyColumns)
            kc.Column.Flags |= DbColumnFlags.PrimaryKey | DbColumnFlags.NoUpdate; 
    }

    protected virtual void LoadReferentialConstraints() {
      var data = GetReferentialConstraintsExt();
      foreach (DataRow row in data.Rows) {
        //Load names for Foreign key and Unique key
        var fkSchema = row.GetAsString("CONSTRAINT_SCHEMA");
        var toSchema = row.GetAsString("UNIQUE_CONSTRAINT_SCHEMA");
        if (!IncludeSchema(toSchema) || !IncludeSchema(fkSchema)) 
          continue;
        var fkTableName = row.GetAsString("C_TABLE");
        var fkName = row.GetAsString("CONSTRAINT_NAME");
        var fromKey = FindKey(fkSchema, fkTableName, fkName);

        var toTableName = row.GetAsString("U_TABLE");
        var toTable = Model.GetTable(toSchema, toTableName);
        Util.Check(toTable != null, "Target table {0}.{1} not found in DB Model.", toSchema, toTableName);
        var toKey = toTable.PrimaryKey; 
/*        
        if (toKey == null) {
          //catch special case - foreign key to non-key set of fields
          if (fromKey != null && fromKey.KeyType.IsSet(KeyType.ForeignKey))
            fromKey.NotSupported = true; 
          continue; //it is not ref constraint
        }
 */ 
        bool cascadeDelete = row.GetAsString("DELETE_RULE") == "CASCADE";
        var refConstraint = new DbRefConstraintInfo(Model, fromKey, toKey, cascadeDelete);
        fromKey.Table.RefConstraints.Add(refConstraint);
      }
    }

    protected virtual DbKeyInfo FindKey(string schema, string tableName, string keyName) {
      var table = Model.GetTable(schema, tableName);
      Util.Check(table != null, "FindKey: table {0}.{1} not found in DB Model.", schema, tableName);
      var key = table.Keys.FirstOrDefault(k => k.Name == keyName);
      return key; 
    }

    protected virtual void LoadIndexes() {
      var data = GetIndexes();
      foreach (DataRow row in data.Rows) {
        var schema = row.GetAsString("TABLE_SCHEMA");
        if (!IncludeSchema(schema)) continue;
        var tableName = row.GetAsString("TABLE_NAME");
        var table = Model.GetTable(schema, tableName);
        if (table == null)
          continue;
        var indexName = row.GetAsString("INDEX_NAME");
        //indexName might be null for SQL Server (type_desc="HEAP") - just ignore these
        if (string.IsNullOrWhiteSpace(indexName)) 
          continue; 
        //primary keys are added through table constraints, so skip them here - except mark them as clustered
        var isPk = IsTrueOrNonZero(row, "primary_key");
        bool isClustered = Driver.Supports(DbFeatures.ClusteredIndexes) && IsTrueOrNonZero(row, "clustered");             
        if (isPk && isClustered) 
            table.PrimaryKey.KeyType = KeyType.ClusteredPrimaryKey; 
        if (isPk)
            continue; //PKs are added through constraints
        var isUnique = IsTrueOrNonZero(row, "unique");
        // Find existing, or create a new one
        var key = table.Keys.FindByName(indexName);
        //If key not exists yet, create it
        if (key == null)
          key = new DbKeyInfo(indexName, table, KeyType.Index);
        else
          key.KeyType |= KeyType.Index; 
        if (isClustered)
          key.KeyType |= KeyType.ClusteredIndex;
        if (isUnique)
          key.KeyType |= KeyType.UniqueIndex;
        key.Filter = row.GetAsString("FILTER_CONDITION");
      }//while
    }

    protected virtual void LoadIndexColumns() {
      var data = GetIndexColumns();
      foreach (DataRow row in data.Rows) {
        var schema = row.GetAsString("TABLE_SCHEMA");
        if (!IncludeSchema(schema)) continue;
        var tableName = row.GetAsString("TABLE_NAME");
        var table = Model.GetTable(schema, tableName);
        if (table == null) 
          continue;
        var index_name = row.GetAsString("INDEX_NAME");
        var colName = row.GetAsString("COLUMN_NAME");
        var colOrdPos = row.GetAsInt("column_ordinal_position");
        var key = table.Keys.FindByName(index_name);
        if (key == null) {
          //LogError("Loading index columns: failed to find owner index/key {0} for column {1}, table {2}.", index_name, colName, tableName);
          continue;
        }
        var dbCol = table.Columns.FindByName(colName);
        if (dbCol == null) continue;
        var desc = row.GetAsInt("IS_DESCENDING");
        bool isDesc = desc == 1;
        var oldKeyCol = key.KeyColumns.FirstOrDefault(kc => kc.Column == dbCol);
        if(oldKeyCol == null) {
          //MS SQL only - supports 'include' columns; for these columns value of ord position is zero
          if(colOrdPos == 0)
            key.IncludeColumns.Add(dbCol); 
          else 
            key.KeyColumns.Add(new DbKeyColumnInfo(dbCol, desc: isDesc));
        } else
          oldKeyCol.Desc = isDesc; 
      }//foreach row
    }//method

    protected virtual void LoadSequences() {
      if (!Settings.Driver.Supports(DbFeatures.Sequences))
        return; 
      var data = GetSequences();
      foreach(DataRow row in data.Rows) {
        var schema = row.GetAsString("SEQUENCE_SCHEMA");
        var name = row.GetAsString("SEQUENCE_NAME");
        var dataType = row.GetAsString("DATA_TYPE");
        var startValue = row.GetAsLong("START_VALUE");
        var incr = row.GetAsInt("INCREMENT");
        //find type def
        var typeDef = Driver.TypeRegistry.FindVendorDbTypeInfo(dataType);
        var typeSpec = typeDef.FormatTypeSpec(0, 0, 0);
        var typeInfo = new DbTypeInfo(typeDef, dataType, false, 0, 0, 0, typeDef.DefaultColumnInit);
        // add sequence
        var seq = new DbSequenceInfo(this.Model, name, schema, typeInfo, startValue, incr);
        Model.AddSequence(seq); 
      }
    }

    protected virtual void LoadUserDefinedTypes() {
      var data = GetUserDefinedTypes();
      foreach (DataRow row in data.Rows) {
        var schema = row.GetAsString("TYPE_SCHEMA");
        var name = row.GetAsString("TYPE_NAME");
        var typeInfo = new DbCustomTypeInfo(this.Model, schema, name);
        this.Model.AddCustomType(typeInfo);
      }
    }


    protected static bool IsTrueOrNonZero(DataRow row, string columnName) {
      var value = row[columnName];
      if (value == null || value == DBNull.Value) return false;
      var t = value.GetType();
      if (t == typeof(bool))
        return (bool)value;
      if (t.IsInt()) {
        var iv = Convert.ToInt32(value);
        return iv != 0;
      }
      return false;
    }

    public virtual string GetProcedureTag(string procText) {
      if(procText == null) return null;
      var tagStart = procText.IndexOf(DbDriver.GeneratedCrudProcTagPrefix);
      if(tagStart < 0)
        return null;
      var tagValueStart = tagStart + DbDriver.GeneratedCrudProcTagPrefix.Length;
      var tagValueEnd = procText.IndexOfAny(new char[] { '\r', '\n' }, tagValueStart);
      if(tagValueEnd < 0)
        return null;
      var tag = procText.Substring(tagValueStart, tagValueEnd - tagValueStart).Trim();
      return tag;
    }

    public virtual DataTable ExecuteSelect(string sql) {
      IDbConnection connection = null;
      IDbCommand cmd = null;
      try {
        connection = Driver.CreateConnection(Settings.SchemaManagementConnectionString);
        connection.Open(); 
        cmd = connection.CreateCommand();
        cmd.CommandText = sql;
        var reader = (IDataReader)Driver.ExecuteCommand(cmd, DbExecutionType.Reader);
        var table = new DataTable();
        //Have to put table into Dataset to disable constraints - see below about SQL CE
        var ds = new DataSet();
        ds.EnforceConstraints = false;
        ds.Tables.Add(table);
        table.Load(reader); 
        return table;
      } finally {
        if(cmd != null)
          cmd.Connection = null; 
        if (connection != null)
          connection.Close();
      }
    }

  }//class

}
